﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class RC_CommandBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void insert(string pen_name, string record_status,int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@pen_name", pen_name);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@created_by", created_by);
              //  dbManager.AddParameters(3, "@created_when", DateTime.Today.ToString() );
           
  
                string sql;
   //             sql = " insert into hrm_dc_penalty(pen_name,record_status,created_by,created_when) "
   //+ " values(@pen_name,@record_status,@created_by,@created_when)";


                sql = " insert into hrm_dc_penalty(pen_name,record_status,created_by) "
+ " values(@pen_name,@record_status,@created_by)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }



        public DataTable Select()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
                    + " cmd.cmd_name,"
                    + " grp.cmd_group_name,"
                    + " type.cmd_type_name,"
                    + " grp.cmd_group_seq,"
                    + " type.cmd_type_seq,"
                    + " cmd.approved_name,"
                    + " cmd.approved_positions,"
                    + " cmd.cmd_ref_no,"
                    + " cmd.approved_date,"
                    + " cmd.cmd_seq"
                    + " FROM hrm_ps_commands cmd"
                    + " left Join hrm_ps_command_types AS type ON cmd.cmd_type_seq = type.cmd_type_seq"
                    + " left Join hrm_ps_command_groups AS grp ON type.cmd_group_seq = grp.cmd_group_seq"
                    + " where type.cmd_group_seq in(1,2,3,4) and"
                    + " cmd.record_status = '" + SystemInfo._ActiveRecordStatus   + "'";
                                        

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectEmpTypeCombo()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_ps_employee_types  "

                    + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectInvestigationCombo()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_dc_investigations  "

+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }





        public DataTable SelectComboGroup()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_ps_command_groups "

+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and cmd_group_seq in(1,2,3,4)";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public DataTable SelectComboType(int group_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_ps_command_types "

+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and cmd_group_seq = " + group_seq ;


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }




        public void Update(int pen_seq,string pen_name, string record_status, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@pen_name", pen_name);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@pen_seq", pen_seq);
                dbManager.AddParameters(3, "@updated_by", updated_by);
               dbManager.AddParameters(4, "@updated_when", DateTime.Today);


                string sql;
       
                sql = " update hrm_dc_penalty "
         + "set pen_name=@pen_name,record_status=@record_status,updated_by=@updated_by,updated_when=@updated_when "
         + " where pen_seq=@pen_seq";


   

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {
            }

            finally
            {
                dbManager.Dispose();

            }

        }


     
        public void Delete(int pen_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@pen_seq", pen_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
             
                sql = " update hrm_dc_penalty set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where pen_seq = @pen_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
      
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }







     

   
    }
}
